/*********************************************************************************
 *      Copyright:  (C) 2022 South-Central Minzu University
 *                  All rights reserved.
 *
 *       Filename:  sqliteall.c
 *    Description:  This file :Client database API operation function
 *                 
 *        Version:  1.0.0(12/04/22)
 *         Author:  zhangkang <2980640573@qq.com>
 *      ChangeLog:  1, Release initial version on "12/04/22 23:41:51"
 *                 
 ********************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>


#include "sqlite3.h"
#include "logger.h"
#define  table_name "cli_temp"

static sqlite3    *db=NULL;


int callback(void *NotUsed, int argc, char **argv, char **azColName) 
{
   int i;
   for(i=0; i<argc; i++)
   {
      log_info("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   return 0;
}

int sqlite_init(char * db_name)      
{
    char                     buf_table[128];
    char                    *zErrMsg = NULL;
    
    
    if( !db_name )
    {
        log_error("ERROR: Invalid input arguments\n");
        return -1;
    }
    
                 /* Open database */
    if( SQLITE_OK != sqlite3_open(db_name,&db) )
    {
        log_error("Error open database: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -2;
    }
    
                        /*Creat table*/    
    memset(buf_table,0,sizeof(buf_table));
    snprintf(buf_table, sizeof(buf_table),"CREATE TABLE %s(ID INTEGER PRIMARY KEY AUTOINCREMENT, SN CHAR(20),DATIME CHAR(100),TEMPERATURE CHAR(50));", table_name);
	  if( SQLITE_OK != sqlite3_exec(db, buf_table, callback, 0, &zErrMsg))
	  {
        log_warn("create table error:%s\n", zErrMsg);
		    sqlite3_free(zErrMsg);
        return -3;
	  }
    else
    {
        log_info("create table scuessfully\n");
        return 0;
    }
    
}

int sqlite_insert_data(char* SN,char* datatime,char* buff_temp)      
{
    char 	 *zErrMsg = NULL;
    char    buf_insert[256]; 
    
    
    memset(buf_insert,0,sizeof(buf_insert));
    snprintf(buf_insert,sizeof(buf_insert),"INSERT INTO %s VALUES (NULL, '%s', '%s', '%s');",table_name,SN,datatime,buff_temp);
   	
    if(  SQLITE_OK !=sqlite3_exec(db, buf_insert, callback, 0, &zErrMsg) )
	  {
      	printf("sqlite inset table error: %s\n", zErrMsg);
      	sqlite3_free(zErrMsg);
        return -1;
   	}
    log_info("insert data successful\n");
	  return 0;
}

int  sqlite_get_count(void)          
{
    int                      maxid=0;
    char                     buf_getmaxid[128];
    char                    *zErrMsg = NULL;
    char                   **result;
    int                      rownum;
    int                      colnum;
    
    if( !db )
    {
        log_error("ERROR: SQLite database not initialise.\n");
        return -1;
    }
 
    memset(buf_getmaxid, 0, sizeof(buf_getmaxid));
    snprintf(buf_getmaxid, sizeof(buf_getmaxid), "select count(*) from %s;", table_name);
 
    if( SQLITE_OK != sqlite3_get_table(db, buf_getmaxid, &result, &rownum, &colnum, &zErrMsg) )
    {
        log_error("query records count from database failure: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -2;
    }
 
 
    if(  rownum <= 0 )
    {
        log_error("query records count from database failure: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -3;
    }
 
    maxid = atoi(result[1*colnum]);
 
    return maxid;
}

int sqlite_get_data(char * buf_send,int buf_size,int maxid)      
{
    char                     buf_getdata[128];
    char                   **result;
    int                      rownum;
    int                      colnum;
    char                    *zErrMsg=NULL;
    
    
    memset(buf_getdata,0,sizeof(buf_getdata));
    snprintf(buf_getdata,sizeof(buf_getdata),"select * from %s limit 1 offset %d;",table_name,maxid-1);
    
    if( SQLITE_OK != sqlite3_get_table(db,buf_getdata,&result,&rownum, &colnum, &zErrMsg) )
    {
        log_error("get maxid data faliure :%s\n ",zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }
    
    snprintf(buf_send,128,"%s/%s/%s",result[1*colnum+1],result[1*colnum+2],result[1*colnum+3]);
    
    return 0;
}

int sqlite_deldata(int i)                  
{
    char   buf_deldata[128];
    char  *zErrMsg = NULL;
   
    memset(buf_deldata,0,sizeof(buf_deldata));
    snprintf(buf_deldata,sizeof(buf_deldata),"DELETE from %s where ID=%d;SELECT * from %s",table_name,i,table_name);
    
    if( SQLITE_OK != sqlite3_exec(db,buf_deldata,0,0,&zErrMsg) )
   	{
        log_error("delete data from sqlite error: %s\n", zErrMsg);
     	  sqlite3_free(zErrMsg);
        return -1;
  	}
    log_info("delete successful\n");
    return 0 ;
}

int sqlite_close_db(void)                 
{
    char 	*zErrMsg = NULL;
    
    if( SQLITE_OK != sqlite3_close(db) )
    {
        log_error("Error close database: %s\n", zErrMsg );
        sqlite3_free(zErrMsg);
    }   
    log_info("successfully close database\n");
    return 0;
}